<?
class mdl_usulan_mp extends CI_Model{
	
	function __construct(){
		parent::__construct();
	}
	
	function getData($num=0, $offset=0){
		$thn=$this->session->userdata("thnanggaran");
		$this->db->flush_cache();
		$this->db->select('*', false);
		$this->db->from('tbl_rpijm');
		$array = array('MpOn' => '1', 'TahunAnggaran' => $thn);
		$this->db->where($array); 
	
		
		$this->db->limit($num, $offset);
		$this->db->order_by('id');
		
		return $this->db->get();
		
	}
	
	function getDataEdit($id){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('tbl_rpijm');
		$this->db->where('id', $id);
		
		return $this->db->get();
	}


	
	function update($data){
		$this->db->flush_cache();
        
        /*$this->db->set('TglCreate', $data['TglCreate']);
        $this->db->set('kd_akun', $data['kd_akun']);
		$this->db->set('kd_wilayah', $data['kd_wilayah']);
		$this->db->set('uraian', $data['uraian']);
		$this->db->set('detailLokasi', $data['detailLokasi']);
		*/
		$this->db->set('MpOn', $data['MpOn']);
		
		$this->db->where('id', $data['id']);
		
		$result = $this->db->update('tbl_rpijm');
		
		if($result) {
			return TRUE;
		}else {
			return FALSE;
		}
		
	}
	
	function getMpOnEdit(){
		$thn=$this->session->userdata("thnanggaran");
		$this->db->flush_cache();
		$this->db->select('*', false);
		$this->db->from('tbl_rpijm');
		$this->db->where('MpOn', '1'); 
		$this->db->where('TahunAnggaran', $thn); 
	
		
		$this->db->order_by('id');
		
		return $this->db->get();
	}
	
	
	function getall() {
		$query = $this->db->query('SELECT * FROM mrpijm');
		$table = $this->table->generate($query);
		return $table;
	}
  
	function rpijm_listing($start, $rows, $search, $sort, $sort_dir) {
		if (!empty($sort) && !empty($sort_dir)) $this->db->order_by($sort, $sort_dir);
		if ($rows <> -1) $this->db->limit($rows, $start);
		$q = $this->db->get('mrpijm');
		#echo $this->db->last_query();
		return $q;
	}
	
	#where = array('field1'=>'data','field2'=>'data')
	function getRpijm($where, $groupby, $start=0, $rows='10', $search='', $sort='', $sort_dir='') {
		$sql	= " SELECT rkegiatan.kd_kegiatan, rkegiatan.nama_kegiatan, '' as output, '' as sub_output, '' as itemkeg, '' as kabkota, '' as lokasirinci, 
						SUM( sd_apbn_rpmurni ) AS apbn, 
						SUM( sd_apbn_pln ) AS pln, 
						SUM( sd_apbn_hln ) AS hibah, 
						SUM(  `sd_apbd_prop` ) AS apbd1, 
						SUM( `sd_apbd_kabkota` ) AS apbd2, 
						SUM( sd_pdam ) AS pdam, 
						SUM( sd_swasta ) AS swasta, 
						SUM( sd_masyarakat ) AS masy, 
						SUM( sd_dak ) AS dak
					FROM rkegiatan 
					LEFT JOIN mrpijm ON SUBSTRING( kd_akun, 1, 4 ) = rkegiatan.kd_kegiatan 
					";
		
		$sql .= $where;
		$sql .= ' GROUP BY '.$groupby;
		$sql .= ' HAVING (apbn + pln + hibah + apbd1 + apbd2 + pdam + swasta + masy + dak) > 0 ';
		if ($rows <> -1) $sql .=  ' LIMIT '.$start.' , '.$rows;
		
		$query 	= $this->db->query($sql); #echo $this->db->last_query();
		
		return $query->result();
	}
	
	function getSubOutputbykdOutput($select, $join, $where, $groupby, $orderby) {
		 
		$sql	= " SELECT ".$select.", ";
		if (empty($groupby)){
			$sql   .= " 	musulanmp.sd_apbn_rpmurni AS apbn, 
							musulanmp.sd_apbn_pln AS pln, 
							musulanmp.sd_apbn_hln AS hibah, 
							musulanmp.sd_apbd_prop AS apbd1, 
							musulanmp.sd_apbd_kabkota AS apbd2, 
							musulanmp.sd_pdam AS pdam, 
							musulanmp.sd_swasta AS swasta, 
							musulanmp.sd_masyarakat AS masy, 
							musulanmp.sd_dak AS dak ,
							musulanmp.id_usulanmp";		
		} else {
			$sql   .= " 	SUM( musulanmp.sd_apbn_rpmurni ) AS apbn, 
							SUM( musulanmp.sd_apbn_pln ) AS pln, 
							SUM( musulanmp.sd_apbn_hln ) AS hibah, 
							SUM( musulanmp.sd_apbd_prop ) AS apbd1, 
							SUM( musulanmp.sd_apbd_kabkota ) AS apbd2, 
							SUM( musulanmp.sd_pdam ) AS pdam, 
							SUM( musulanmp.sd_swasta ) AS swasta, 
							SUM( musulanmp.sd_masyarakat ) AS masy, 
							SUM( musulanmp.sd_dak ) AS dak,
							musulanmp.id_usulanmp ";
		}
		$sql   .= " FROM mrpijm ";
		
		if (!empty($join)) {
			foreach ($join as $pKey) {
				switch ($pKey) {
					case "rkegiatan":
						$sql .= ' LEFT JOIN rkegiatan ON SUBSTRING( mrpijm.kd_akun, 1, 4 ) = rkegiatan.kd_kegiatan ';
						break;
					case "routput":
						$sql .= ' LEFT JOIN routput ON SUBSTRING( mrpijm.kd_akun, 6, 3 ) = kd_output AND rkegiatan.kd_kegiatan = routput.kd_kegiatan ';
						$sql .= ' INNER JOIN musulanmp ON musulanmp.id_rpijm = mrpijm.id_rpijm ';
						break;
					case "rsuboutput":
						$sql .= 'LEFT JOIN rsuboutput ON SUBSTRING( mrpijm.kd_akun, 10, 3 ) = kd_suboutput 
									AND rkegiatan.kd_kegiatan = rsuboutput.kd_kegiatan 
									AND routput.kd_output = rsuboutput.kd_output ';
						break;
					case "rkomponen":
						$sql .= 'LEFT JOIN rkomponen ON SUBSTRING( mrpijm.kd_akun, 14, 3 ) = kd_komponen 
									AND rkegiatan.kd_kegiatan = rkomponen.kd_kegiatan 
									AND routput.kd_output = rkomponen.kd_output 
									AND rsuboutput.kd_suboutput = rkomponen.kd_suboutput
									';
					case "rkabkota":
						$sql .= 'LEFT JOIN rkabkota ON SUBSTRING( mrpijm.kd_wilayah, 4, 2 ) = rkabkota.kd_kabkota 
									AND rkabkota.kd_propinsi = SUBSTRING( mrpijm.kd_wilayah, 1, 2 ) ';
						break;
				}
			}
		}
		
		if (!empty($where)) {
			$sql   .= ' WHERE '. $where;
		}
		
		if (!empty($groupby)) $sql .= ' GROUP BY '.$groupby;
		$sql .= ' ORDER BY '.$orderby;
		
		$query 	= $this->db->query($sql); #echo $this->db->last_query();

		return $query->result();
	}
	
	function getSubOutputbykdOutput2($select, $join, $where, $groupby, $orderby) {
		$sql	= " SELECT ".$select.", ";
		if (empty($groupby)){
			$sql   .= " 	musulanmp.sd_apbn_rpmurni AS apbn, 
							musulanmp.sd_apbn_pln AS pln, 
							musulanmp.sd_apbn_hln AS hibah, 
							musulanmp.sd_apbd_prop AS apbd1, 
							musulanmp.sd_apbd_kabkota AS apbd2, 
							musulanmp.sd_pdam AS pdam, 
							musulanmp.sd_swasta AS swasta, 
							musulanmp.sd_masyarakat AS masy, 
							musulanmp.sd_dak AS dak ,
							musulanmp.id_usulanmp";		
		} else {
			$sql   .= " 	SUM( musulanmp.sd_apbn_rpmurni ) AS apbn, 
							SUM( musulanmp.sd_apbn_pln ) AS pln, 
							SUM( musulanmp.sd_apbn_hln ) AS hibah, 
							SUM( musulanmp.sd_apbd_prop ) AS apbd1, 
							SUM( musulanmp.sd_apbd_kabkota ) AS apbd2, 
							SUM( musulanmp.sd_pdam ) AS pdam, 
							SUM( musulanmp.sd_swasta ) AS swasta, 
							SUM( musulanmp.sd_masyarakat ) AS masy, 
							SUM( musulanmp.sd_dak ) AS dak,
							musulanmp.id_usulanmp ";
		}
		$sql   .= " FROM mrpijm ";
		
		if (!empty($join)) {
			foreach ($join as $pKey) {
				switch ($pKey) {
					case "rkegiatan":
						$sql .= ' LEFT JOIN rkegiatan ON SUBSTRING( mrpijm.kd_akun, 1, 4 ) = rkegiatan.kd_kegiatan ';
						break;
					case "routput":
						$sql .= ' LEFT JOIN routput ON SUBSTRING( mrpijm.kd_akun, 6, 3 ) = kd_output AND rkegiatan.kd_kegiatan = routput.kd_kegiatan ';
						$sql .= ' INNER JOIN musulanmp ON musulanmp.id_rpijm = mrpijm.id_rpijm ';
						break;
					case "rsuboutput":
						$sql .= 'LEFT JOIN rsuboutput ON SUBSTRING( mrpijm.kd_akun, 10, 3 ) = kd_suboutput 
									AND rkegiatan.kd_kegiatan = rsuboutput.kd_kegiatan 
									AND routput.kd_output = rsuboutput.kd_output ';
						break;
					case "rkomponen":
						$sql .= 'LEFT JOIN rkomponen ON SUBSTRING( mrpijm.kd_akun, 14, 3 ) = kd_komponen 
									AND rkegiatan.kd_kegiatan = rkomponen.kd_kegiatan 
									AND routput.kd_output = rkomponen.kd_output 
									AND rsuboutput.kd_suboutput = rkomponen.kd_suboutput
									';
					case "rkabkota":
						$sql .= 'LEFT JOIN rkabkota ON SUBSTRING( mrpijm.kd_wilayah, 4, 2 ) = rkabkota.kd_kabkota 
									AND rkabkota.kd_propinsi = SUBSTRING( mrpijm.kd_wilayah, 1, 2 ) ';
						break;
				}
			}
		}
		
		if (!empty($where)) {
			$sql   .= ' WHERE '. $where;
		}
		
		if (!empty($groupby)) $sql .= ' GROUP BY '.$groupby;
		$sql .= ' ORDER BY '.$orderby;
		 
  

		return $this->db->query($sql); #echo $this->db->last_query();
	}
	
	function kriteriabyIdRipjm($IdRipjm) {
		$this->db->where('trpijm_kkesiapan.id_rpijm', $IdRipjm);
		$this->db->where('mrpijm.usulan_mp', '1');//BARU
		$this->db->select('rkriteriakesiapan.kd_kriteria, rkriteriakesiapan.nama_kriteria');
		$this->db->from('mrpijm');
		$this->db->join('trpijm_kkesiapan', 'mrpijm.id_rpijm = trpijm_kkesiapan.id_rpijm');
		$this->db->join('rkriteriakesiapan', 'rkriteriakesiapan.kd_kriteria	 = trpijm_kkesiapan.kd_kriteria	');

		$q = $this->db->get()->result();
		return $q;
	}
	
	function process_update($id,$data){
		 
		$this->db->flush_cache();
		$this->db->where('id_usulanmp', $id);
		$this->db->update('musulanmp', $data);
		redirect('update_mp');
	}
	
	function process_delete($id){
		 
		$this->db->flush_cache();
		$this->db->delete('musulanmp', array('id_usulanmp' => $id));
		redirect('update_mp');
	}
}
?>